import os
import pymysql
import json

from paramiko.client import SSHClient, AutoAddPolicy

import run_request_service as rrs
import paramiko as pm
import datetime
import diff_error_file as file

mainDb = pymysql.connect(host='localhost', port=23306, user='root', password='Pr0dkcd_', db="kcdoc")
db_cursor = mainDb.cursor()

difDb = pymysql.connect(host='10.2.5.100', port=13306, user='root', password='kc@#2023++', db="kcdoc")
dif_cursor = difDb.cursor()

target_host = '172.16.10.221'
target_port = 22
target_username = 'root'
target_password = 'Kc@123456'

# 创建SSH客户端对象
ssh_client1 = pm.SSHClient()
ssh_client1.set_missing_host_key_policy(pm.AutoAddPolicy())
ssh_client1.connect('127.0.0.1', port=22022, username='root', password='Kc@123456')
transport = ssh_client1.get_transport()
dest_addr = (target_host, target_port)
local_addr = ('', 0)
channel = transport.open_channel("direct-tcpip", dest_addr, local_addr)

target_client = SSHClient()
target_client.set_missing_host_key_policy(AutoAddPolicy())
target_client.connect(target_host, port=target_port, username=target_username, password=target_password, sock=channel)
mainFtp = target_client.open_sftp()

# 创建SSH客户端对象
ssh_client2 = pm.SSHClient()
ssh_client2.set_missing_host_key_policy(pm.AutoAddPolicy())
ssh_client2.connect('10.2.5.100', port=9999, username='root', password='Kcadmin@123456')
appFtp = ssh_client2.open_sftp()
import requests

session = requests.Session()


def login(token):
    url = "http://127.0.0.1:22080/kcdoc/delegateLogin"
    payload = {'token': token}
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    response = session.post(url, data=payload, headers=headers)
    data = response.text
    print(data)


def updatye_plan(plan_ids):
    url = "http://127.0.0.1:22080/kcdoc/exportPlan/batchFreshStatu"
    headers = {'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'}
    data = {
        "planCode": plan_ids,
        "executorName": "1"
    }
    response = session.post(url, data=data, headers=headers)
    data = response.text
    print(data)


def end_plan_funtion(plan_ids):
    url = " http://127.0.0.1:22080/kcdoc/exportPlan/batchOverPlan"
    headers = {'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'}
    data = {
        "planCode": plan_ids,
        "reason": "计划已经结束",
        "applyNo": "14282",
        "oaCode": ""
    }
    response = session.post(url, data=data, headers=headers)
    data = response.text
    print(data)


def user_to_task(task_id, owner, handlerUser):
    url = "http://127.0.0.1:22080/kcdoc/activiti/kctask/updateTaskUser"
    headers = {'Content-Type': 'application/json;charset=UTF-8'}
    data = {
        "taskId": task_id,
        "owner": owner,
        "handlerUser": handlerUser
    }

    response = session.post(url, json=data, headers=headers)
    data = response.text
    print(data)


def get_error_prosson_id():
    error_ids = []
    get_error_sql = """
      SELECT
            plan.id 
        FROM
            kc_production_plan plan
            LEFT JOIN kc_production_plan_process kppp ON plan.id = kppp.production_plan_id 
        WHERE
            LEFT ( plan.production_code, 2 ) in('JF','SS','FX','PG')
            AND kppp.state IN ( 1, 10 ) 
        GROUP BY
            kppp.state,
            plan.id 
        HAVING
            count( plan.id ) > 1;
    """
    db_cursor.execute(get_error_sql)
    fetchall = db_cursor.fetchall()
    for e in fetchall:
        error_ids.append(e)

    return error_ids


def get_error_trIds():
    error_ids = []
    get_tr_error_sql = """
        SELECT
            plan.id 
        FROM
            kc_production_plan plan
            LEFT JOIN kc_process_trajectory tra ON plan.id = tra.production_plan_id 
        WHERE
            LEFT ( plan.production_code, 2 )  in('JF','SS','PG')
            AND tra.state IN ( 1, 10 ) 
        GROUP BY
            tra.state,
            plan.id 
        HAVING
            count( plan.id ) > 1;
    """
    db_cursor.execute(get_tr_error_sql)
    fetchall = db_cursor.fetchall()
    for e in fetchall:
        error_ids.append(e)

    return error_ids


def get_error_prsson():
    prsson_error = []
    get_error_sql = """
    SELECT
        kppp.id 
    FROM
        kc_production_plan plan
        LEFT JOIN kc_production_plan_process kppp ON plan.id = kppp.production_plan_id 
    WHERE
        ( kppp.assign_time IS NULL OR kppp.arrivals_time IS NULL OR kppp.process_time IS NULL ) 
        AND LEFT ( plan.production_code, 2 )  in('JF','SS','FX','PG')
        AND kppp.state IN ( 2 );
    """
    db_cursor.execute(get_error_sql)
    fetchall = db_cursor.fetchall()
    for e in fetchall:
        prsson_error.append(e)

    return prsson_error


def get_error_state():
    out_list = {}
    error_ps_ids = []
    error_tr_ids = []
    get_error_tr_ps = """
        SELECT
            tra.id trId,
            prss.id prssId 
        FROM
            kc_production_plan_process prss
            LEFT JOIN kc_process_trajectory tra ON prss.production_plan_id = tra.production_plan_id
            LEFT JOIN kc_production_plan plan ON plan.id = prss.production_plan_id 
        WHERE
            prss.arrivals_time <> tra.arrivals_time 
            AND ABS(
            TIMESTAMPDIFF( SECOND, prss.arrivals_time, tra.arrivals_time )) > 3600 
            AND LEFT ( plan.production_code, 2 )  in('JF','SS','FX','PG')
            AND prss.id = tra.process_id UNION ALL
        SELECT
            tra.id trId,
            prss.id prid 
        FROM
            kc_production_plan_process prss
            LEFT JOIN kc_process_trajectory tra ON prss.production_plan_id = tra.production_plan_id
            LEFT JOIN kc_production_plan plan ON plan.id = prss.production_plan_id 
        WHERE
            prss.state <> tra.state 
            AND LEFT ( plan.production_code, 2 )  in('JF','SS','FX','PG')
            AND prss.id = tra.process_id
    """
    db_cursor.execute(get_error_tr_ps)
    fetchall = db_cursor.fetchall()
    for e in fetchall:
        trId = e[0]
        prssId = e[1]
        error_ps_ids.append(prssId)
        error_tr_ids.append(trId)

    out_list["trId"] = error_tr_ids
    out_list["psId"] = error_ps_ids
    if len(error_tr_ids) > 0:
        return out_list
    else:
        return None


#  从手机端获取数据
def getPrssonPlanId(planIds):
    query_result = []
    query = "SELECT id,state,process_time,process_consuming,time_consuming,IFNULL(assign_time,arrivals_time) assign_time,arrivals_time FROM kc_production_plan_process WHERE   state = 2 and production_plan_id IN (%s)" % (
        ','.join(['%s'] * len(planIds)))
    dif_cursor.execute(query, planIds)
    fetchall = dif_cursor.fetchall()
    for e in fetchall:
        data_info = {"id": e[0], "state": e[1], "process_time": e[2], "process_consuming": e[3], "time_consuming": e[4],
                     "assign_time": e[5], "arrivals_time": e[6]}
        query_result.append(data_info)
    return query_result


def getProcessId(planIds):
    query_result = []
    query = "SELECT id,state,process_time,process_consuming,time_consuming,IFNULL(assign_time,arrivals_time) assign_time,arrivals_time FROM kc_production_plan_process WHERE   state = 2 and id IN (%s)" % (
        ','.join(['%s'] * len(planIds)))
    dif_cursor.execute(query, planIds)
    fetchall = dif_cursor.fetchall()
    for e in fetchall:
        data_info = {"id": e[0], "state": e[1], "process_time": e[2], "process_consuming": e[3], "time_consuming": e[4],
                     "assign_time": e[5], "arrivals_time": e[6]}
        query_result.append(data_info)
    return query_result


def getTraPlanId(planIds):
    query_result = []
    query = (
            "SELECT production_plan_id,process_id,state,process_time,process_consuming,time_consuming FROM kc_process_trajectory WHERE  state in( 10,2)"
            "and production_plan_id IN (%s)" % (','.join(['%s'] * len(planIds))))
    dif_cursor.execute(query, planIds)
    fetchall = dif_cursor.fetchall()
    for e in fetchall:
        data_info = {"production_plan_id": e[0], "process_id": e[1], "state": e[2], "process_time": e[3],
                     "process_consuming": e[4],
                     "time_consuming": e[5]}
        query_result.append(data_info)
    return query_result


# 获取update数据
def generate_update_statements(query_result, table_name):
    update_statements = []
    for row in query_result:
        if all(value is not None for value in row.values()):
            update_values = ', '.join([f"{key} = '{value}'" for key, value in row.items() if key != 'id'])
            update_statement = f"UPDATE {table_name} SET {update_values} WHERE id = {row['id']};"
            update_statements.append(update_statement)
    return update_statements


# 转化成插入语句
def generate_insert_statements(query_result, table_name):
    insert_statements = []
    for row in query_result:
        columns = ', '.join(row.keys())
        values = ', '.join([f"'{value}'" if value is not None else "NULL" for value in row.values()])
        insert_statement = f"INSERT INTO {table_name} ({columns}) VALUES ({values});"
        insert_statements.append(insert_statement)
    return insert_statements


def get_update_plan():
    all_plan = []
    get_plan = """
         SELECT
            plan.production_code 
        FROM
            kc_process_trajectory tra
            LEFT JOIN kc_production_plan plan ON tra.production_plan_id = plan.id  and plan.trId = tra.id 
            LEFT JOIN kc_process_att_relation kpar ON tra.id = kpar.trId 
        WHERE
            LEFT ( plan.production_code, 2 ) in('JF','SS','PG')
            AND tra.process_name = '签署入库回单' 
            AND plan.showState IS NOT NULL 
            AND kpar.attid IS NOT NULL
            UNION ALL
         SELECT
            plan.production_code 
        FROM kc_production_plan plan
            LEFT JOIN  	kc_process_trajectory tra ON tra.production_plan_id = plan.id and plan.trId = tra.id 
            LEFT JOIN kc_process_att_relation kpar ON tra.id = kpar.trId 
        WHERE
            LEFT ( plan.production_code, 2 )  in('JF','SS','PG')
            AND tra.process_name = '签署入库回单' 
            AND plan.showState IS NULL 
            AND kpar.attid IS NULL
             UNION ALL
        SELECT
            plan.production_code
        FROM
            kc_process_trajectory tra
            LEFT JOIN kc_production_plan plan ON tra.production_plan_id = plan.id
            LEFT JOIN kc_process_att_relation kpar ON tra.id = kpar.trId 
        WHERE
            LEFT ( plan.production_code, 2 )  in('JF','SS')
            AND tra.process_name = '签署入库回单' 
            AND plan.showState = '不满足(扩展属性未填写)' 
            AND kpar.attid IS NOT NULL
    """
    db_cursor.execute(get_plan)
    fetchall = db_cursor.fetchall()
    for e in fetchall:
        all_plan.append(e[0])

    return all_plan


def update_sql_tr_pss(dataInfo):
    trIds = dataInfo["trId"]
    psIds = dataInfo["psId"]
    trIds_str = ','.join(map(str, trIds))
    psIds_str = ','.join(map(str, psIds))
    update_sql = """
        UPDATE kc_production_plan_process prss
        INNER JOIN kc_process_trajectory tra on prss.id = tra.process_id
        SET prss.state = tra.state,
        prss.arrivals_time = tra.arrivals_time,
        prss.assign_time = tra.assign_time,
        prss.process_time = tra.process_time,
        prss.process_consuming = tra.process_consuming,
        prss.time_consuming = tra.time_consuming 
        WHERE tra.id IN ({0}) AND prss.id IN ({1})
    """.format(trIds_str, psIds_str)
    db_cursor.execute(update_sql)
    mainDb.commit()


def instat_tr_data(saveInfo):
    delet_sql = """ DELETE FROM kcdoc_temp.error_tr_data"""
    db_cursor.execute(delet_sql)
    mainDb.commit()
    for sql in saveInfo:
        db_cursor.execute(sql)
        mainDb.commit()


def update_main_data_sql(saveInfo):
    for sql in saveInfo:
        db_cursor.execute(sql)
        mainDb.commit()


def get_all_plan():
    run_all_plan = []
    rm_all_plan = []
    get_run = """ select production_code from kc_production_plan"""
    dif_cursor.execute(get_run)
    fetchall = dif_cursor.fetchall()
    for e in fetchall:
        run_all_plan.append(e[0])

    allPlanCode = '\',\''.join(map(str, run_all_plan))
    newAllPlan = "'" + allPlanCode + "'"
    get_end_plan = """ 
      select production_code from hi_kc_production_plan where production_code in ({0})
       union all
      select production_code from kc_production_plan_history where production_code in ({0})
     """.format(newAllPlan)
    db_cursor.execute(get_end_plan)
    endAllPlanInfo = db_cursor.fetchall()
    for e in endAllPlanInfo:
        rm_all_plan.append(e[0])

    return rm_all_plan


def rm_plan_info(rm_all_plan):
    for plan in rm_all_plan:
        end_sql = """  DELETE FROM kc_production_plan where  production_code = '{0}';""".format(plan)
        print(end_sql)
        dif_cursor.execute(end_sql)
        difDb.commit()


def updateTrData():
    updateSql = """
    UPDATE kc_process_trajectory tra,
        kcdoc_temp.error_tr_data etd 
        SET tra.state = etd.state,
        tra.process_time = etd.process_time,
        tra.process_consuming = etd.process_consuming,
        tra.time_consuming = etd.time_consuming 
        WHERE
            tra.process_id = etd.process_id 
            AND tra.production_plan_id = etd.production_plan_id
    """
    db_cursor.execute(updateSql)
    mainDb.commit()


def update_plan_info(plan_id):
    join = ','.join(map(str, plan_id))
    rrs.login('712017165048788d32c953896cc7ce4a')
    rrs.updatye_plan(join)


def diff_file():
    recent_date = ((datetime.datetime.now()) + datetime.timedelta(days=-6)).strftime("%Y-%m-%d %H:%M:%S")
    file.diff_file(recent_date)


def diff_jf_data():
    sql_query = """
              SELECT
                  CONCAT(
                      'INSERT INTO `kcdoc`.`kc_step_attr` (`refId`, `code`, `name`, `value`, `type`, `modify`, `val_type`) ',
                      'VALUES (', IFNULL(tra.id, 'NULL'), ', \'warehousingReceipt\', \'ccc\', \'1\', \'3\', NULL, NULL);'
                  )
              FROM
                  kc_production_plan plan
                  LEFT JOIN kc_production_plan_process kpp ON plan.process = kpp.id 
                  LEFT JOIN kc_process_trajectory tra ON tra.id = plan.trId
                  LEFT JOIN kc_step_attr stt ON tra.id = stt.refId AND stt.type = 3
              WHERE
                  LEFT(plan.production_code, 2) = 'JF' 
                  AND plan.showState = '不满足(扩展属性未填写)'
          """
    db_cursor.execute(sql_query)
    results = db_cursor.fetchall()
    for result in results:
        insert_statement = result[0]
        print(f"Executing: {insert_statement}")

        try:
            db_cursor.execute(insert_statement)
        except pymysql.err.ProgrammingError as e:
            print(f"Error executing SQL: {e}")
            # Optionally, log the error or handle it as needed.

    mainDb.commit()


def diff_file_list(mainPath, appPath):
    missing_files = []
    flag = True
    try:
        mainFtp.stat(mainPath)
        appFtp.stat(appPath)
    except FileNotFoundError:
        flag = False

    if flag:
        mainFile = mainFtp.listdir(mainPath)
        listdir = appFtp.listdir(appPath)
        for file1 in listdir:
            if file1 not in mainFile:
                missing_files.append(file1)

    return missing_files


def insert_tr_value():
    run_sql = """
          SELECT
            DISTINCT CONCAT('INSERT INTO `kcdoc`.`kc_step_attr` ( `refId`, `code`, `name`, `value`, `type`, `modify`, `val_type`) VALUES (',plan.trId,' ,\'warehousingReceipt\', \'入库回单\', \'1\', 3, NULL, NULL);')	 
        FROM
            kc_production_plan plan
            LEFT JOIN kc_production_plan_process kppp ON plan.id = kppp.production_plan_id 
            AND plan.process = kppp.id
            WHERE LEFT(production_code,2)= 'JF'
            and plan.showState = '不满足(扩展属性未填写)'
            and kppp.process_name = '签署入库回单'
      """
    db_cursor.execute(run_sql)
    fetchall = db_cursor.fetchall()
    for e in fetchall:
        db_cursor.execute([e])
        mainDb.commit()


def get_date():
    recent_dates = []
    current_date = datetime.now()
    for i in range(40):
        dateInfo = {}
        recent_date = current_date - datetime.timedelta(days=i)
        mainFilePath = recent_date.strftime('%Y%m%d')
        appFilePath = recent_date.strftime('%Y-%m-%d')
        dateInfo["main"] = mainFilePath
        dateInfo["app"] = appFilePath
        recent_dates.append(dateInfo)
    return recent_dates


def getEndFxPlan():
    getFXSql = """
        SELECT
            fxp.production_code fxPlanCode
        FROM
            kc_production_plan plan
            LEFT JOIN kc_production_plan fxp ON plan.id = fxp.start_at_plan_id
            LEFT JOIN kc_production_plan_process fxpp ON fxpp.id = fxp.process
            LEFT JOIN kc_production_plan_process kpp ON fxp.end_at_process_id = kpp.id
            LEFT JOIN kc_production_plan_process skpp ON fxp.start_at_process_id = skpp.id 
        WHERE
            LEFT ( plan.production_code, 2 ) = 'JF' 
            AND LEFT ( fxp.production_code, 2 ) = 'FX'
            and  (kpp.state = 2 or 	skpp.state = 2)
            and  fxpp.process_name = '厂检/总检'
            and fxp.production_code != 'FX24-01040'
    """
    db_cursor.execute(getFXSql)
    fxInfoList = db_cursor.fetchall()
    all_fx_plan = [plan[0] for plan in fxInfoList]
    if all_fx_plan:
        joined_fx_plan = ','.join(all_fx_plan)
        rrs.end_plan_funtion(joined_fx_plan)


if __name__ == '__main__':

    processIds = get_error_prosson_id()
    if len(processIds) > 0:
        processInfo = getPrssonPlanId(processIds)
        statements = generate_update_statements(processInfo, 'kc_production_plan_process')
    print("------处理process数据问题-----------")

    trIds = get_error_trIds()
    if len(trIds) > 0:
        trInfos = getTraPlanId(trIds)
        insert_trSql = generate_insert_statements(trInfos, 'kcdoc_temp.error_tr_data')
        instat_tr_data(insert_trSql)
        updateTrData()
    print("------处理tr数据-----------")
    process_Ids = get_error_prsson()
    if len(process_Ids) > 0:
        process_Infos = getProcessId(process_Ids)
        update_statements = generate_update_statements(process_Infos, 'kc_production_plan_process')
        update_main_data_sql(update_statements)
    print("------处理时间存在问题数据-----------")
    state = get_error_state()
    if state is not None:
        update_sql_tr_pss(state)
    print("------处理计划状态-------")
    # diff_jf_data()
    print("------处理时间存在问题数据-----------")
    plan = get_update_plan()
    update_plan_info(plan)
    print(json.dumps(plan))
    print("------同步文件-----------")
    diff_file()
    print("------删除计划信息-----------")
    rm_plan = get_all_plan()
    rm_plan_info(rm_plan)
    print("------处理未结束交付信息-------")
    getEndFxPlan()
